<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class M_tanya_model extends arfaModel {
    function __construct() {
        parent::__construct();
		$this->table_name	= "master_pertanyaan";
		$this->primary_key	= "ID_PERTANYAAN";
		$this->order_by		= "ID_PERTANYAAN ASC";
    }

    public function get_key_pertanyaan() {
        $this->db->select('*');
        $this->db->from($this->table_name);
        $this->db->where('STATUS', '1');

        $query = $this->db->get();

        $data = array();
        if (count($query) > 0) {

            foreach ($query->result_array() as $row) {
                $data[$row['ID_PERTANYAAN']] = $row['PERTANYAAN'];
            }
        }

        return $data;
    }

    function get_nilai_dinas($tgl_awal,$tgl_akhir,$id_ijin,$id_tanya){
        $query  = $this->db->query("
            SELECT
            dt_pertanyaan.ID_IJIN,
            dt_pertanyaan.ID_PERTANYAAN,
            SUM(kuesioner_jawaban.nilai) /COUNT(kuesioner.kd_kuesioner) AS nilai
            FROM
            dt_pertanyaan
            JOIN kuesioner_jawaban ON kuesioner_jawaban.ID_PERTANYAAN=dt_pertanyaan.ID_PERTANYAAN
            JOIN kuesioner ON kuesioner_jawaban.kd_kuesioner = kuesioner.kd_kuesioner
            WHERE dt_pertanyaan.ID_PERTANYAAN = " . $id_tanya . " AND kuesioner.ID_IJIN = " . $id_ijin . " AND kuesioner.TGL_KUESIONER >= '" . $tgl_awal . "' AND kuesioner.TGL_KUESIONER <= '" . $tgl_akhir . "'
        ");
        return $query->row_array();
    }

    function get_nilai_periode($tgl_awal,$tgl_akhir,$id_pertanyaan = FALSE){
        $query  = $this->db->query("
            SELECT
            YEAR(TGL_KUESIONER) AS TAHUN
            FROM `kuesioner`
            JOIN kuesioner_jawaban ON kuesioner_jawaban.kd_kuesioner = kuesioner.kd_kuesioner
            WHERE
            TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
            GROUP BY TAHUN"
        );
        return $query->result_array();
    }

    function get_nilai_periode2($tahun,$id_pertanyaan){
        $query  = $this->db->query("
            SELECT
            YEAR(TGL_KUESIONER) AS TAHUN,
            ID_PERTANYAAN,
            SUM(nilai) / COUNT(kuesioner.kd_kuesioner) as nilai
            FROM `kuesioner`
            JOIN kuesioner_jawaban ON kuesioner_jawaban.kd_kuesioner = kuesioner.kd_kuesioner
            WHERE
            TGL_KUESIONER LIKE '" . $tahun . "%' AND ID_PERTANYAAN = " . $id_pertanyaan . " GROUP BY TAHUN
        ");
        return $query->row_array();
    }

    function get_report_ikm_unsur($tgl_awal,$tgl_akhir) {
        $query  = $this->db->query("
            SELECT
            master_pertanyaan.URUTAN as NOMOR,
            master_pertanyaan.PERTANYAAN,
            SUM(kuesioner_jawaban.nilai)/COUNT(kuesioner.kd_kuesioner) AS NILAI
            FROM `master_pertanyaan`
            JOIN kuesioner_jawaban ON master_pertanyaan.ID_PERTANYAAN = kuesioner_jawaban.ID_PERTANYAAN
            JOIN kuesioner ON kuesioner.kd_kuesioner = kuesioner_jawaban.kd_kuesioner
            WHERE
            TGL_KUESIONER >= '" . $tgl_awal . "' AND TGL_KUESIONER <= '" . $tgl_akhir . "'
            GROUP BY master_pertanyaan.URUTAN"
        );
        return $query->result();
    }
}